﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Utils;
using DataHelper;
//--工号、年、月、出勤天数、备注、加班时数、是否全勤、餐费、迟到、早退、矿工、事假、病假、婚假、丧假、产假
// FEmpNumber,Fyear,FMonth,FWorkDay,fExplanation,FEndEmpBuXiuHRest,FQqSign,FCf,FCd,FZt,Fkg,FShiJia,FBingJia,FHunJia,FsangJia,FChanJia,FEndEmpBuXiuHRest 
namespace DAL
{
   public class FromKingdeeDao
    {
        //FID,
       string fields = " FDeptName as 部门,FEmpNumber as 工号,FEmpName as 姓名,FzwName as 职务,cast(FBeginDate as varchar(10)) as 入职日期,FQqSign as 全勤,(Case When FCd=0 Then Null Else FCd End) as 迟到,(Case When FCdTimes=0 Then Null Else FCdTimes End) as 迟到次数,(Case When FZt=0 Then Null Else FZt End) as 早退,(Case When FZtTimes=0 Then Null Else FZtTimes End) as 早退次数,Fcf as 餐费,(Case When FKg=0 Then Null Else FKg End) as 旷工,(Case When FShiJia=0 Then Null Else FShiJia End) as 事假,(Case When FBingJia=0 Then Null Else FBingJia End) as 病假,(Case When FHunJia=0 Then Null Else FHunJia End) as 婚假,(Case When FSangJia=0 Then Null Else FSangJia End) as 丧假,(Case When FChanJia=0 Then Null Else FChanJia End) as 产假 ";
       //要从考勤服务器获取
       public DataTable GetListsFromKq(string strYear,string strMonth)
       {
           return KQSQLHelper.ExecuteSqlGetDataSet("exec STKD_Kq_Month_P 0," + strYear + "," + strMonth + ",'','','',0,$0.0000,$0.0000,0,$0.0000,119,'全部','全部',0,'全部'").Tables[0];//正常
       
       }
       
       //下面都是操作本地
       public bool IsExistKq(string strYear, string strMonth)
       {
           DataTable dt = GetLists(strYear, strMonth);
           if (dt.Rows.Count >= 1)
               return true;
           else//否则
               return false;
       }
       public bool DeleteKq(string strYear, string strMonth)
       {
           return SQLHelper.ExecuteSql("delete from Wage_FromKingdee where  Fyear=" + strYear + " and Fmonth=" + strMonth);
       }
     
       public DataTable GetLists(string strYear, string strMonth)
       {
           return SQLHelper.ExecuteSqlGetDrToDt("select " + fields + " from Wage_FromKingdee where Fyear=" + strYear + " and Fmonth=" + strMonth);
       }
       public DataTable GetLists(string empCode,string strYear, string strMonth)
       {
           if (!string.IsNullOrWhiteSpace(empCode))
               return SQLHelper.ExecuteSqlGetDrToDt("select top 60 " + fields + " from Wage_FromKingdee where FEmpNumber like '%" + empCode + "%' and FEmpName like '%" + empCode + "%'");
           else
               return SQLHelper.ExecuteSqlGetDrToDt("select " + fields + " from Wage_FromKingdee where Fyear=" + strYear + " and Fmonth=" + strMonth + " order by FDeptName ASC,FEmpNumber ASC");
       }
       public bool BulkInsert(DataTable  sourceTable)
       {
           bool flag=true;
           try
           {
               string conn = XmlUtility.GetConnFormConfig("JIMConn");
               
               SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn);
               sqlBulkCopy.DestinationTableName = "Wage_FromKingdee";
               sqlBulkCopy.BatchSize = 1000;
               SqlConnection sqlConnection = new SqlConnection(conn);
               sqlConnection.Open();
               if (sourceTable != null && sourceTable.Rows.Count > 0)
               {
                   sqlBulkCopy.WriteToServer(sourceTable);
               }
               sqlBulkCopy.Close();   
               sqlConnection.Close();
           }
           catch
           {
               flag = false;
           }
           return flag;
       }
       //获取新员工信息
       public DataTable  GetNewEmpList()
       {
           string strSQL = "select EmpCode as FNumber,EmpName as FName,EmpCode as FGzNumber,EmpCode as FWlkh,DeptID,0 as FDeptID,0 as FCx,DutyID,0 as FZwID,(case EmpSex when '男' then 1 else 0 end) as FSex,JoinTime as FBeginDate,StateID,0 as FZysxID,2 as FKjID ,'补休' as FAddWorkType1 ,'补休' as FAddWorkType2 from Admin_Employee where JoinTime>='" + System.DateTime.Now.AddMonths(-2).ToString("yyyy-MM-dd") + "'";
           return SQLHelper.ExecuteSqlGetDrToDt(strSQL);
       }
       //把员工信息导入考勤软件
       public bool BulkOut(DataTable sourceTable)
       {
           bool flag = true;
           try
           {
               string conn = XmlUtility.GetConnFormConfig("KingDeeKQ");

               SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn);
               sqlBulkCopy.DestinationTableName = "[Swap_HrEmp]";
               sqlBulkCopy.BatchSize = 100;
               SqlConnection sqlConnection = new SqlConnection(conn);
               sqlConnection.Open();
               if (sourceTable != null && sourceTable.Rows.Count > 0)
               {
                   sqlBulkCopy.WriteToServer(sourceTable);
               }
               sqlBulkCopy.Close();
               sqlConnection.Close();
           }
           catch
           {
               flag = false;
           }
           return flag;
       }
       public bool UpdateOut()
       {
           StringBuilder builder = new StringBuilder();
           builder.Append("Update t1 set t1.FDeptID=t2.FItemID from  Swap_HrEmp t1,STKD_Kq_DeptEmp t2 where t1.DeptID=t2.HRDeptID;");
           builder.Append("Update t1 set t1.FZwID=t2.FItemID from  Swap_HrEmp t1,STKD_Kq_ZwEmp t2 where t1.DutyID=t2.HRDutyID;");
           builder.Append("Update t1 set t1.FZysxID=t2.FItemID from  Swap_HrEmp t1,STKD_Kq_ZysxEmp t2 where t1.StateID=t2.HRStateID;");

           builder.Append("INSERT INTO STKD_Kq_Emp (FNumber,FName,FGzNumber,FWlkh,FDeptID,FCx,FZwID,FSex,FBeginDate,FZysxID,FKjID,FAddWorkType1,FAddWorkType2) select distinct FNumber,FName,FGzNumber,FWlkh,FDeptID,FCx,FZwID,FSex,FBeginDate,FZysxID,FKjID,FAddWorkType1,FAddWorkType2 from  Swap_HrEmp where FNumber not in (SELECT FNumber FROM STKD_Kq_Emp);");
           builder.Append("truncate table Swap_HrEmp;");
           builder.Append("Update STKD_Kq_Emp Set FBingJia=0,FGongXiu=0,FNianJia=0,Fglf=0, FdeptIDMonth=FDeptID where FdeptIDMonth=0;");


           return KQSQLHelper.ExecuteSql(builder.ToString());
              
       }
    }
}
